package cn.jhc.db;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.jhc.bean.User;

//增删改查 C(Create)R(Retrieve)U(Update)D(Delete)
public class UserDao {
	private static final String CREATEQL = "insert into user(username, pass, phone) "
			+ "values(?, ?, ?)";
	private static final String SELECTALL = "select id,username,pass,phone from user";
	private static final String DELETEQL = "delete from user where id=?";
	private static final String FINDBYIDQL = "select id,username,pass,phone from user where id=?";
	private static final String LOGINQL = "select id,username,phone from user where username=? and pass=?";
	
	private QueryRunner runner;
	
	public UserDao(QueryRunner runner) {
		super();
		this.runner = runner;
	}

	public void create(User user) throws SQLException {
//		Connection connection = null;
//		try {
//			connection = getConnection();
//			PreparedStatement statement = connection.prepareStatement(CREATEQL);
//			statement.setString(1, user.getUsername());
//			statement.setString(2, user.getPass());
//			statement.setString(3, user.getPhone());
//			statement.execute();
//		} catch (SQLException e) {
//			throw e;
//		} finally {
//			connection.close();
//		}
		runner.execute(CREATEQL, user.getUsername(), user.getPass(), user.getPhone());
	}
	
	public void update(User user) {
		
	}
	
	public User login(String username, String password) throws SQLException {
		ResultSetHandler<User> resultSetHandler = new BeanHandler<>(User.class);
		return runner.query(LOGINQL, resultSetHandler, username, password);
	}
	
	public void delete(int id) throws SQLException {
		runner.execute(DELETEQL, id);
	}
	
	public User findById(int id) throws SQLException {
		ResultSetHandler<User> resultSetHandler = new BeanHandler<>(User.class);
		return runner.query(FINDBYIDQL, resultSetHandler, id);
	}
	
	public List<User> find() throws SQLException {
//		List<User> users = new ArrayList<>();
//		Connection connection = null;
//		try {
//			connection = getConnection();
//			Statement statement = connection.createStatement();
//			ResultSet resultSet = statement.executeQuery(SELECTALL);
//			while (resultSet.next()) {
//				String username = resultSet.getString("username");
//				int id = resultSet.getInt("id");
//				String pass = resultSet.getString("pass");
//				String phone = resultSet.getString("phone");
//				users.add(new User(id, username, pass, phone));
//			}
//		} catch (SQLException e) {
//			throw e;
//		} finally {
//			connection.close();
//		}
//		return users;
		ResultSetHandler<List<User>> resultSetHandler = new BeanListHandler<>(User.class);
		return runner.query(SELECTALL, resultSetHandler);
	}

}
